/**
 * 数据库初始化脚本
 * 运行此脚本来创建数据库和表结构
 */

require('dotenv').config()
const mysql = require('mysql2/promise')
const fs = require('fs').promises
const path = require('path')

// 数据库配置
const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 3306,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '0519',
  charset: 'utf8mb4'
}

const dbName = process.env.DB_NAME || 'pc_wechat_chat'

async function initDatabase() {
  let connection = null
  
  try {
    console.log('🔄 开始初始化数据库...')
    
    // 连接到MySQL服务器（不指定数据库）
    connection = await mysql.createConnection(dbConfig)
    console.log('✅ 连接到MySQL服务器成功')
    
    // 创建数据库
    await connection.execute(`CREATE DATABASE IF NOT EXISTS \`${dbName}\` DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci`)
    console.log(`✅ 数据库 ${dbName} 创建成功`)
    
    // 关闭当前连接，重新连接到目标数据库
    await connection.end()

    // 重新连接到目标数据库
    connection = await mysql.createConnection({
      ...dbConfig,
      database: dbName
    })
    console.log(`✅ 连接到数据库 ${dbName}`)

    // 直接执行表创建语句
    console.log('🔄 创建数据表...')

    // 创建用户表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS users (
        id int(11) NOT NULL AUTO_INCREMENT,
        wxid varchar(50) NOT NULL UNIQUE,
        nickname varchar(100) NOT NULL,
        password varchar(255) NOT NULL,
        icon varchar(255) DEFAULT 'face0.jpeg',
        sex enum('male','female','robot') DEFAULT 'male',
        summary text,
        remark varchar(100),
        area varchar(100),
        letter char(1),
        robot tinyint(1) DEFAULT 0,
        status enum('online','offline','busy') DEFAULT 'offline',
        last_login timestamp NULL,
        created_at timestamp DEFAULT CURRENT_TIMESTAMP,
        updated_at timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY idx_wxid (wxid),
        KEY idx_status (status)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    `)
    console.log('✅ 用户表创建成功')

    // 创建会话表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS conversations (
        id int(11) NOT NULL AUTO_INCREMENT,
        type enum('private','group') DEFAULT 'private',
        name varchar(100),
        avatar varchar(255),
        created_by int(11),
        created_at timestamp DEFAULT CURRENT_TIMESTAMP,
        updated_at timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    `)
    console.log('✅ 会话表创建成功')

    // 创建会话参与者表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS conversation_participants (
        id int(11) NOT NULL AUTO_INCREMENT,
        conversation_id int(11) NOT NULL,
        user_id int(11) NOT NULL,
        role enum('member','admin','owner') DEFAULT 'member',
        joined_at timestamp DEFAULT CURRENT_TIMESTAMP,
        left_at timestamp NULL,
        PRIMARY KEY (id),
        UNIQUE KEY uk_conversation_user (conversation_id, user_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    `)
    console.log('✅ 会话参与者表创建成功')

    // 创建消息表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS messages (
        id int(11) NOT NULL AUTO_INCREMENT,
        conversation_id int(11) NOT NULL,
        from_user_id int(11) NOT NULL,
        to_user_id int(11),
        content text NOT NULL,
        type enum('text','image','file','audio','video','emoji','system') DEFAULT 'text',
        file_url varchar(500),
        file_name varchar(255),
        file_size int(11),
        file_type varchar(50),
        is_recalled tinyint(1) DEFAULT 0,
        recalled_at timestamp NULL,
        created_at timestamp DEFAULT CURRENT_TIMESTAMP,
        updated_at timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY idx_conversation_id (conversation_id),
        KEY idx_from_user_id (from_user_id),
        KEY idx_created_at (created_at)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    `)
    console.log('✅ 消息表创建成功')

    // 创建消息已读状态表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS message_reads (
        id int(11) NOT NULL AUTO_INCREMENT,
        message_id int(11) NOT NULL,
        user_id int(11) NOT NULL,
        read_at timestamp DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        UNIQUE KEY uk_message_user (message_id, user_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    `)
    console.log('✅ 消息已读状态表创建成功')

    // 创建文件存储表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS files (
        id int(11) NOT NULL AUTO_INCREMENT,
        original_name varchar(255) NOT NULL,
        stored_name varchar(255) NOT NULL,
        file_path varchar(500) NOT NULL,
        file_size int(11) NOT NULL,
        file_type varchar(50) NOT NULL,
        mime_type varchar(100) NOT NULL,
        uploaded_by int(11) NOT NULL,
        created_at timestamp DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        UNIQUE KEY uk_stored_name (stored_name)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    `)
    console.log('✅ 文件存储表创建成功')

    // 创建好友关系表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS friendships (
        id int(11) NOT NULL AUTO_INCREMENT,
        user_id int(11) NOT NULL,
        friend_id int(11) NOT NULL,
        status enum('pending','accepted','blocked') DEFAULT 'accepted',
        created_at timestamp DEFAULT CURRENT_TIMESTAMP,
        updated_at timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        UNIQUE KEY uk_user_friend (user_id, friend_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    `)
    console.log('✅ 好友关系表创建成功')

    // 插入初始用户数据
    console.log('🔄 插入初始用户数据...')
    const users = [
      [0, 'goddlts', '教瘦', '123456', 'face0.jpeg', 'male', '没有留下内容', '教瘦', '河北 邯郸', 'J', 0],
      [1, 'aaa123', '阿玛尼', '123456', 'face2.webp', 'male', '没有留下内容', '阿玛尼', '上海 浦东', 'A', 0],
      [2, 'balala123', '巴拉拉', '123456', 'face3.webp', 'male', '两只小蜜蜂啊，飞到花丛中啊', '巴拉拉', '北京 昌平', 'B', 0],
      [3, 'dalao', '大佬', '123456', 'face10.webp', 'male', '无', '大佬', '北京 朝阳', 'D', 0],
      [4, 'fadadaxyz', '法大大', '123456', 'face4.webp', 'male', '无', '法大大', '北京 朝阳', 'F', 0],
      [5, 'jiajia1998', '佳佳', '123456', 'face5.webp', 'female', '美女老师佳佳', '佳佳', '北京 朝阳', 'J', 0],
      [6, 'jijijiij', '叽叽叽叽', '123456', 'face6.webp', 'male', '无', '叽叽叽叽', '北京 北京', 'J', 0],
      [7, 'meinv1990', '美女', '123456', 'face1.webp', 'female', '美女无才便是德', '美女', '北京 北京', 'M', 0],
      [8, 'mengmeng123', '萌萌', '123456', 'face8.webp', 'male', '无', '萌萌', '北京 北京', 'M', 0],
      [9, 'miaomiaomiao', '喵喵喵', '123456', 'face7.webp', 'male', '无', '喵喵喵', '北京 北京', 'M', 0],
      [10, 'dabai', '大白机器人', '123456', 'robot.webp', 'robot', '我是一个智能机器人', '大白机器人', '中国 China', 'D', 1]
    ]

    for (const user of users) {
      try {
        await connection.execute(`
          INSERT IGNORE INTO users (id, wxid, nickname, password, icon, sex, summary, remark, area, letter, robot)
          VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        `, user)
      } catch (error) {
        if (!error.message.includes('Duplicate entry')) {
          console.warn(`⚠️  插入用户数据警告: ${error.message}`)
        }
      }
    }
    console.log('✅ 用户数据插入完成')

    // 插入好友关系数据
    console.log('🔄 插入好友关系数据...')
    const friendships = [
      [0, 1], [1, 0], [0, 2], [2, 0], [0, 3], [3, 0],
      [0, 4], [4, 0], [0, 5], [5, 0], [0, 6], [6, 0],
      [0, 7], [7, 0], [0, 8], [8, 0], [0, 9], [9, 0],
      [0, 10], [10, 0], [1, 10], [10, 1], [2, 10], [10, 2]
    ]

    for (const friendship of friendships) {
      try {
        await connection.execute(`
          INSERT IGNORE INTO friendships (user_id, friend_id)
          VALUES (?, ?)
        `, friendship)
      } catch (error) {
        if (!error.message.includes('Duplicate entry')) {
          console.warn(`⚠️  插入好友关系警告: ${error.message}`)
        }
      }
    }
    console.log('✅ 好友关系数据插入完成')

    console.log('✅ 数据库初始化完成')
    
    // 验证表是否创建成功
    const [tables] = await connection.execute('SHOW TABLES')
    console.log(`✅ 成功创建 ${tables.length} 个表:`)
    tables.forEach(table => {
      console.log(`   - ${Object.values(table)[0]}`)
    })
    
    // 检查用户数据
    const [userCount] = await connection.execute('SELECT COUNT(*) as count FROM users')
    console.log(`✅ 用户表中有 ${userCount[0].count} 个用户`)
    
    console.log('🎉 数据库初始化完成！')
    
  } catch (error) {
    console.error('❌ 数据库初始化失败:', error.message)
    process.exit(1)
  } finally {
    if (connection) {
      await connection.end()
      console.log('🔌 数据库连接已关闭')
    }
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  initDatabase()
}

module.exports = { initDatabase }
